using Microsoft.VisualBasic;
using System;
using System.Collections;
using System.Diagnostics;
using System.Drawing;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;
namespace ePlanSysOman
{
  public class clsData
  {
     
    public DataSet getData(string SQL)
    {
        DataSet tempds = new DataSet();//JMR -- DEV -- look at -- because getData = dataSet sets getData to return dataSet at the END of the function, I am moving the return statement down to the bottom and returning the tempdataSet --http://stackoverflow.com/questions/26469027/setting-a-function-equal-to-something/26469050?noredirect=1#comment41576085_26469050
      
      OleDbDataAdapter da = new OleDbDataAdapter();
      OleDbCommand cmd = new OleDbCommand();
      DataSet ds = new DataSet(); 
      Application.DoEvents();
      //Dim filecn As String

      //filecn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & ni


      //InitConnection()

      //Open connectionnn
      try {
          Module1.newModule.InitConnection();
        //  cn.ConnectionString = filecn
        //
        //"Provider=Microsoft.Jet.OLEDB.4.0; Data Source"

        //C:\Documents and Settings\USER\Επιφάνεια εργασίας\ePlan\Planning\bin\Debug\data.mdb"

        //cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\Projects.NET\ePlan\Planning\bin\Debug\data.mdb"

        //
        //"Provider=Microsoft.Jet.OLEDB.4.0; Data Source"

        //C:\Documents and Settings\USER\Επιφάνεια εργασίας\ePlan\Planning\bin\Debug\data.mdb"

        //cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\Projects.NET\ePlan\Planning\bin\Debug\data.mdb"
        //cn.Open()

        //Retrieve data
          cmd.Connection = Module1.newModule.Cn;
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = SQL;
        da.SelectCommand = cmd;
        da.Fill(ds);
        //Me.ErrorNumber = 0

        //Return rows
        //getData = ds;//JMR -- DEV -- look at -- changed it to the line below --http://stackoverflow.com/questions/26469027/setting-a-function-equal-to-something
        tempds = ds;        
          
      } catch (Exception ex) {
        // Me.ErrorNumber = -1
      } finally {
        //Dispose objects
          //Module1.newModule.Cn.Close();
          //Module1.newModule.Cn.Dispose();
        cmd.Dispose();
        da.Dispose();
        //Module1.newModule.Cn = null;
        cmd = null;
        da = null;

      }
      tempds = ds;  
      return tempds;//JMR -- DEV -- Moved from above
    }

    public void ExecuteSQL(string SQL)
    {

      Application.DoEvents();
      OleDbConnection connection = new OleDbConnection();
      OleDbCommand command = new OleDbCommand();
      try {
          connection.ConnectionString = (("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + Module1.newModule.ni));
        connection.Open();
        command.Connection = (connection);
        command.CommandType = (CommandType.Text);
        command.CommandText = (SQL);
        command.ExecuteNonQuery();
      } catch (Exception exception1) {

        Exception exception = exception1;
        if (Module1.newModule.ROUTEWIRES)
        {
          try {
            SQL = string.Format("UPDATE tblBackShopWires SET bacRefDes2 = '{0}',bacPin2 ='{1}',bacTerm2 ='{2}' where (bacWireNo = '{3}' and bacPlId = '{4}' and bacAction ='{5}'and bacPin2<>'{6}' and bacRefDes2 <>'{7}' ) ", new object[] {
              Module1.newModule.end1,
              Module1.newModule.Pin,
              Module1.newModule.Term,
              Module1.newModule.wireno1,
              Module1.newModule.plnumber,
              Module1.newModule.ACTION,
              Module1.newModule.Pin,
              Module1.newModule.end1
            });
            command.Connection = (connection);
            command.CommandType = (CommandType.Text);
            command.CommandText = (SQL);
            command.ExecuteNonQuery();
            if ((Module1.newModule.ACTION == "ADD"))
            {
                if (Module1.newModule.Pin.Contains("SP") && Module1.newModule.wireno1.Contains("SHB"))
                {
              } else {

                SQL = string.Format("UPDATE tblCONT SET bacRefDes2 = '{0}',bacPin2 ='{1}',bacTerm2 ='{2}' where (bacWireNo = '{3}' and bacPlId = '{4}' and bacAction ='{5}' ) ", new object[] {
                  Module1.newModule.end1,
                  Module1.newModule.Pin,
                  Module1.newModule.Term,
                  Module1.newModule.wireno1,
                  Module1.newModule.plnumber,
                  Module1.newModule.ACTION
                });
                command.Connection = (connection);
                command.CommandType = (CommandType.Text);
                command.CommandText = (SQL);
                command.ExecuteNonQuery();
              }

            }
            try {
                if ((Module1.newModule.ACTION == "ADD"))
                {
                SQL = string.Format("UPDATE TBLWIRESADD SET ROUTE ='{0}' where (WireNo = '{1}' and WIRESPlId = '{2}' and WIREACTID ='{3}' and SREFDES <> '{4}') ", new object[] {
                  Module1.newModule.end1,
                  Module1.newModule.wireno1,
                  Module1.newModule.plnumber,
                  Module1.newModule.ACTION,
                  Module1.newModule.end1
                });
                command.Connection = (connection);
                command.CommandType = (CommandType.Text);
                command.CommandText = (SQL);
                command.ExecuteNonQuery();
              }
            } catch (Exception ex) {

            }
            try {
                if ((Module1.newModule.ACTION == "REMOVE"))
                {
                SQL = string.Format("UPDATE TBLWIRESREMOVE SET ROUTE ='{0}' where (WireNo = '{1}' and WIRESPlId = '{2}' and WIREACTID ='{3}' and SREFDES <> '{4}') ", new object[] {
                  Module1.newModule.end1,
                  Module1.newModule.wireno1,
                  Module1.newModule.plnumber,
                  Module1.newModule.ACTION,
                  Module1.newModule.end1
                });
                command.Connection = (connection);
                command.CommandType = (CommandType.Text);
                command.CommandText = (SQL);
                command.ExecuteNonQuery();
              }
            } catch (Exception ex) {

            }
          } catch (Exception exception4) {

            Exception exception2 = exception4;

          }
        }
        if (Module1.newModule.EcpParts)
        {
          SQL = string.Format("UPDATE TBLASSEMBLY SET prtDwgZone ='{0}',prtFlag ='{3}' WHERE PRTECP = '{1}'  and prtPartNumberDoc ='{2}' AND PRTPLID = '{4}'", new object[] {
            Module1.newModule.DWGZONE,
            Module1.newModule.ECP,
            Module1.newModule.assyPartNumber,
            Module1.newModule.flag,
            Module1.newModule.PlanNumber
          });
          command.Connection = (connection);
          command.CommandType = (CommandType.Text);
          command.CommandText = (SQL);
          command.ExecuteNonQuery();
        }
        if (Module1.newModule.Ecns)
        {
            //JMR -- Look at -- Err.Number property is not populated correctly in C#. Should not rely on it. Suggestion: Try/Catch statement
            if ((Information.Err().Number == 5))
            {
              Module1.newModule.Ecns = false;
          }
          Module1.newModule.Ecns = false;
        }
        if (Module1.newModule.CONT)
        {
            SQL = string.Format("DELETE * FROM TBLCONT WHERE WIRENO ='{0}'", Module1.newModule.wireno1);
          command.Connection = (connection);
          command.CommandType = (CommandType.Text);
          command.CommandText = (SQL);
          command.ExecuteNonQuery();
          Module1.newModule.CONT = false;
        }
        if (Module1.newModule.comp)
        {
          try {
            SQL = string.Concat(new string[] {
              "delete  * from tblwires where ( WIRENO = '",
              Module1.newModule.wireno1,
              "' and wREFDESID = ",
              Module1.newModule.RefDesId.ToString(),
              " and PIN ='",
              Module1.newModule.Pin,
              "' and TERM = '",
              Module1.newModule.Term,
              "' and WIRESPLID = '",
              Module1.newModule.PlanNumber,
              "')"
            });
            command.Connection = (connection);
            command.CommandType = (CommandType.Text);
            command.CommandText = (SQL);
            command.ExecuteNonQuery();
            Module1.newModule.comp = false;
          } catch (Exception exception5) {

            Exception exception3 = exception5;
            // XtraMessageBox.Show(Conversion.ErrorToString)

          }
        }

      } finally {
        connection.Close();
        connection.Dispose();
        command.Dispose();
        connection = null;
        command = null;
      }

    }
    //Public Property ErrorNumber() As Integer

    //    Get
    //        ErrorNumber = pErrorNumber





    //    End Get
    //    Set(ByVal Value As Integer)
    //        pErrorNumber = Value
    //    End Set
    //End Property
  }
}

